﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace lipesin.Main
{
	public static class Dalc
	{
		private const string _connStr = @"Data Source=localhost;Initial Catalog=cash_logger;Integrated Security=True";
		//string connStr = @"Data Source=.\SQLEXPRESS;AttachDbFilename='C:\SQL Databases\cash_logger.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True";
		private static SqlConnection _conn = new SqlConnection(_connStr);
		//public Dalc()
		//{
			
		//}

		public static DataTable ExecuteToDataTable(string sql)
		{
			using (SqlCommand cmd = new SqlCommand())
			{
				cmd.CommandText = sql;
				return ExecuteToDataTable(cmd);
			}
		}

		public static DataTable ExecuteToDataTable(SqlCommand cmd)
		{
			DataTable result;
			using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
			{
				cmd.Connection = _conn;
				result = new DataTable();
				result.ExtendedProperties.Add("command_text", cmd.CommandText);
				adp.Fill(result);
			}
			return result;
		}

		public static void UpdateDataTable(DataTable table)
		{
			using (SqlCommand cmd = new SqlCommand())
			using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
			using (SqlCommandBuilder bld = new SqlCommandBuilder(adp))
			{
				bld.SetAllValues = false;
				bld.ConflictOption = ConflictOption.OverwriteChanges;
				cmd.CommandText = table.ExtendedProperties["command_text"].ToString();
				cmd.Connection = _conn;
				adp.Update(table);
			}
		}

		public static long NextId(int amount = 1, string sequence = "main")
		{
			long result;
			using (SqlCommand cmd = new SqlCommand())
			{
				cmd.Connection = _conn;
				if(_conn.State!=ConnectionState.Open)  _conn.Open();
				cmd.CommandText = "select value from dbo.counters where id='{0}'".F(sequence);
				result = Convert.ToInt64(cmd.ExecuteScalar());
				cmd.CommandText = "update dbo.counters set value = {1} where id='{0}'".F(sequence, result + amount);
				cmd.ExecuteNonQuery();
			}
			return result;
		}

		public static long ExecuteToScalar(string sql)
		{
			long result;
			using (SqlCommand cmd = new SqlCommand())
			{
				cmd.Connection = _conn;
				if (_conn.State != ConnectionState.Open) _conn.Open();
				cmd.CommandText = sql;
				result = Convert.ToInt64(cmd.ExecuteScalar());
			}
			return result;
		}

		public static void ExecuteNonQuery(string sql)
		{
			using (SqlCommand cmd = new SqlCommand())
			{
				cmd.Connection = _conn;
				if (_conn.State != ConnectionState.Open) _conn.Open();
				cmd.CommandText = sql;
				cmd.ExecuteNonQuery();
			}
		}
	}
}
